package model;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
/**
 *
 * @author Солнце
 */
@XmlRootElement
@XmlAccessorType(XmlAccessType.FIELD)
public class Routes{


    public Routes() {
        
    }
    /**
     * Добавление маршрута
     * @param Route flightsRoute Новый маршрут 
     */
    public static int addRoute(Route flightsRoute){
        Connection conn = DataAccess.getConn();
        try{
            PreparedStatement stat = conn.prepareStatement("INSERT INTO route(outpoint,inpoint) VALUES (?,?)",Statement.RETURN_GENERATED_KEYS);
            stat.setInt(1, flightsRoute.getPointDepart().getID());
            stat.setInt(2, flightsRoute.getPointArrive().getID());
             stat.executeUpdate();            
            ResultSet rs = stat.getGeneratedKeys();
            rs.next();
            return rs.getInt(1);
        }
        catch(SQLException ex){
             return 0;
        }
        finally{
           
            try {
               
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
                 return 0;
            }            
           
        }        
    }
    
    public static ArrayList<Route> getRoutesFromRS(ResultSet rs){
         ArrayList<Route> routesFromRS = new ArrayList<Route>();
        try {            
            while (rs.next()){
                Route newRoute = new Route();
                newRoute.setPointArrive(Points.getByID(rs.getInt("outpoint")));
                newRoute.setPointDepart(Points.getByID(rs.getInt("inpoint")));
                newRoute.setID(rs.getInt("id"));
               
                routesFromRS.add(newRoute);
            }            
        } catch (SQLException ex) {
            Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
        }
        return routesFromRS;
    }
        
   public static ArrayList<Route> getAllRoutes() {
        Connection conn = DataAccess.getConn();
        try{
        PreparedStatement stat = conn.prepareStatement("select * from route\n");
        
        ResultSet rs = stat.executeQuery();
        return getRoutesFromRS(rs);
        }
        catch(SQLException ex){
            return null;
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }
    }
   
     public static Route getByID(int id) {
        Connection conn = DataAccess.getConn();
        try{
        PreparedStatement stat = conn.prepareStatement("select * from route\n"
            +"where(id = ?)\n");
        stat.setInt(1, id);
        ResultSet rs = stat.executeQuery();
        rs.next();
        return new Route(rs);
        }
        catch(SQLException ex){
            return null;
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }
    }
     
    public static void deleteRouteByID(int id) {
      Connection conn = DataAccess.getConn();
        try{
        PreparedStatement stat = conn.prepareStatement("DELETE from route\n"
            +"WHERE id = ?");
        stat.setInt(1, id);        
        stat.executeUpdate();                
        }
        catch(SQLException ex){            
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }  
    }

    public static void updateRoute(Route newRoute) {
     Connection conn = DataAccess.getConn();
        try{
        PreparedStatement stat = conn.prepareStatement("UPDATE route\n"
            +" SET outpoint = ?,inpoint = ?\n"
            +"WHERE id = ?");
        stat.setInt(1, newRoute.getPointArrive().getID());        
        stat.setInt(2, newRoute.getPointDepart().getID());        
        stat.setInt(3, newRoute.getID());
        stat.executeUpdate();                
        }
        catch(SQLException ex){            
        }
        finally{
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Flights.class.getName()).log(Level.SEVERE, null, ex);
            }            
        }     
    }
}
